package com.pioneer.item;

import java.lang.Integer;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;

import javax.ejb.EntityBean;
import javax.ejb.EntityContext;

import javax.ejb.CreateException;
import javax.ejb.FinderException;
import javax.ejb.EJBException;

import com.pioneer.util.DBUtil;
import com.pioneer.util.DBException;

public class ItemBean implements EntityBean, ItemInterface
{
	private String item_no;
	private String desc;
	private int qty;
	private int safe_qty;
	private int pieces_case;
	private double price;
	private double invoice_cost;
	private double actual_cost;
	private int vendor_id;
	private EntityContext context;

	public String ejbCreate(String item_no, String desc, int qty, int safe_qty, int pieces_case,
	double price, double invoice_cost, double actual_cost, int vendor_id)
	throws CreateException
	{		
		this.item_no=item_no;
		this.desc=desc;
		this.qty=qty;
		this.safe_qty=safe_qty;
		this.pieces_case=pieces_case;
		this.price=price;
		this.invoice_cost=invoice_cost;
		this.actual_cost=actual_cost;
		this.vendor_id=vendor_id;
		
		Connection dbConnection=null;
		Statement stat=null;
		ResultSet rs=null;
		String sqlString="";		
		try
		{			
			dbConnection = DBUtil.getDBConnection();
			stat=dbConnection.createStatement();
			rs=stat.executeQuery("SELECT * from b01_item WHERE b01_item_no='"+item_no+"'");
			if (rs.next())
			{
				throw new CreateException("Item number conflicts with existing item");
			}
			else
			{
				sqlString="INSERT INTO b01_item (b01_item_no, b01_desc, "+
				"b01_qty, b01_safe_qty,	b01_pieces_case, b01_price, b01_invoice_cost, "+
				"b01_actual_cost, b01_vendor_id) values ('"+item_no+"','"+desc+"','"+qty+
				"','"+safe_qty+"','"+pieces_case+"','"+price+"','"+invoice_cost+"','"+actual_cost+
				"','"+vendor_id+"')";
				int count=stat.executeUpdate(sqlString);
				return item_no;
			}
		}
		catch (DBException e)
		{
			throw new CreateException("Couldn't establish the connection");
		}
		catch (SQLException e)
		{
			throw new CreateException(sqlString);
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
				if (dbConnection!=null) dbConnection.close();
			}
			catch (SQLException e)
			{
				throw new CreateException(e.getMessage());
			}
		}
	}

	public void ejbPostCreate(String item_no, String desc, int qty, int safe_qty, int pieces_case,
	double price, double invoice_cost, double actual_cost, int vendor_id)	{}
	public void ejbActivate() {}
	public void ejbPassivate() {}
	
	public void ejbLoad()
	throws EJBException
	{
		this.item_no=(String)context.getPrimaryKey();
		Connection dbConnection=null;
		Statement stat=null;
		ResultSet rs=null;
		String sqlString="";
		String temp="";
		try
		{
			dbConnection = DBUtil.getDBConnection();
			stat=dbConnection.createStatement();
			sqlString="SELECT b01_desc,b01_qty,b01_safe_qty,b01_pieces_case,b01_price,"+
			"b01_invoice_cost,b01_actual_cost,b01_vendor_id from b01_item WHERE "+
			"b01_item_no='"+this.item_no+"'";			
			rs=stat.executeQuery(sqlString);
			if (rs.next())
			{
				this.desc=rs.getString("b01_desc");
				this.qty=rs.getInt("b01_qty");
				this.safe_qty=rs.getInt("b01_safe_qty");
				this.pieces_case=rs.getInt("b01_pieces_case");
				this.price=rs.getDouble("b01_price");
				this.invoice_cost=rs.getDouble("b01_invoice_cost");
				this.actual_cost=rs.getDouble("b01_actual_cost");
				this.vendor_id=rs.getInt("b01_vendor_id");
			}
			else
			{
				throw new EJBException("Invalid primary key");
			}		
			System.out.println("In load");
		}
		catch (DBException e)
		{
			throw new EJBException("Couldn't establish the connection");
		}
		catch (SQLException e)
		{
			//throw new EJBException("ejbLoad: Couldn't do the refresh because of "+sqlString+ "debug "+temp);
			throw new EJBException("Couldn't do the refresh because of "+sqlString+
			" error code is "+e.getErrorCode()+" sql state is "+e.getSQLState());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
				if (dbConnection!=null) dbConnection.close();
			}
			catch (SQLException e)
			{
				throw new EJBException(e.getMessage());
			}
		}
	}

	public void ejbStore()
	throws EJBException
	{	
		Connection dbConnection=null;
		Statement stat=null;
		ResultSet rs=null;
		String sqlString="";
		try
		{
			dbConnection = DBUtil.getDBConnection();
			stat=dbConnection.createStatement();
			sqlString="UPDATE b01_item set b01_desc='"+this.desc+
								"',b01_qty='"+this.qty+
								"',b01_safe_qty='"+this.safe_qty+"',b01_pieces_case='"+this.pieces_case+
								"',b01_price='"+this.price+"',b01_invoice_cost='"+this.invoice_cost+
								"',b01_actual_cost='"+this.actual_cost+"',b01_vendor_id='"+this.vendor_id+
								"' WHERE b01_item_no='"+this.item_no+"'";
			int resultCount=stat.executeUpdate(sqlString);
		}
		catch (DBException e)
		{
			throw new EJBException("Couldn't establish the connection");
		}
		catch (SQLException e)
		{
			throw new EJBException("Couldn't do the update");
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
				if (dbConnection!=null) dbConnection.close();
			}
			catch (SQLException e)
			{
				throw new EJBException(e.getMessage());
			}
		}
	}

	public void ejbRemove()
	throws EJBException
	{
		Connection dbConnection=null;
		Statement stat=null;
		ResultSet rs=null;
		String sqlString="";
		try
		{
			dbConnection = DBUtil.getDBConnection();
			stat=dbConnection.createStatement();
			sqlString="DELETE FROM b01_item WHERE b01_item_no='"+
								this.item_no+"'";
			int resultCount=stat.executeUpdate(sqlString);			
		}
		catch (DBException e)
		{
			throw new EJBException("Couldn't establish the connection");
		}
		catch (SQLException e)
		{
			throw new EJBException("Couldn't do the delete because of "+sqlString+
			" error code is "+e.getErrorCode()+" sql state is "+e.getSQLState());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
				if (dbConnection!=null) dbConnection.close();
			}
			catch (SQLException e)
			{
				throw new EJBException(e.getMessage());
			}
		}
	}
	
	public String ejbFindByPrimaryKey(String searchkey)
	throws FinderException,EJBException
	{
		Connection dbConnection=null;
		Statement stat=null;
		ResultSet rs=null;
		String sqlString="";
		try
		{
			dbConnection = DBUtil.getDBConnection();
			stat=dbConnection.createStatement();
			sqlString="SELECT * FROM b01_item WHERE b01_item_no='"+searchkey+"'";
			rs=stat.executeQuery(sqlString);
			if (!rs.next())
			{
				throw new FinderException("No matching item number");
			}
			else
			{
				return searchkey;
			}
		}
		catch (DBException e)
		{
			throw new EJBException("Couldn't establish the connection");
		}
		catch (SQLException e)
		{
			throw new EJBException("findbyPK: Couldn't do the refresh because of "+sqlString);
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
				if (dbConnection!=null) dbConnection.close();
			}
			catch (SQLException e)
			{
				throw new EJBException(e.getMessage());
			}
		}	
	}

	public void setEntityContext(EntityContext newContext)
	{
		context=newContext;
	}

	public void unsetEntityContext()
	{
		context=null;
	}

	public String getDesc()
	{
		return this.desc;
	}
	
	public void setDesc(String desc)
	{
		this.desc=desc;
	}
	
	public int getQty()
	{
		return this.qty;
	}
	
	public void setQty(int qty)
	{
		this.qty=qty;
	}
	
	public int getSafeQty()
	{
		return this.safe_qty;
	}
	
	public void setSafeQty(int safeQty)
	{
		this.safe_qty=safeQty;
	}
	
	public int getPiecesCase()
	{
		return this.pieces_case;
	}
	
	public void setPiecesCase(int piecesCase)
	{
		this.pieces_case=piecesCase;
	}

	public double getPrice()
	{
		return this.price;
	}
	
	public void setPrice(double price)
	{
		this.price=price;
	}

	public double getInvoiceCost()
	{
		return this.invoice_cost;
	}
	
	public void setInvoiceCost(double invoiceCost)
	{
		this.invoice_cost=invoiceCost;
	}

	public double getActualCost()
	{
		return this.actual_cost;
	}
	
	public void setActualCost(double actualCost)
	{
		this.actual_cost=actualCost;
	}

	public int getVendorId()
	{
		return this.vendor_id;
	}
	
	public void setVendorId(int vendorId)
	{
		this.vendor_id=vendorId;
	}
}